package com.abc;
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;


public class MySqlFunc {

	Connection connection = null;
	String driverName="com.mysql.jdbc.Driver";
			
	public static String URL="jdbc:mysql://localhost:3306/USER_INFO";
	int USER_ID;
	String USER_NAME,EMAIL,FIRST_NAME,LAST_NAME,PASSWORD,ACTIVE_FLAG,CREATED_BY;
	Date CREATED_DATE;
	 String MODIFIED_BY ;
	Date DOB;
	 int POINTS,NUM_BUSINESS;
	 MySqlFunc(){
		 
	 }
	 MySqlFunc(int USER_ID,String USER_NAME,String EMAIL,String FIRST_NAME,String LAST_NAME,String PASSWORD,String ACTIVE_FLAG,String CREATED_BY,Date CREATED_DATE,String MODIFIED_BY ,Date DOB ,int POINTS,int NUM_BUSINESS){
		 this.USER_ID=USER_ID;
		 
		this.USER_NAME=USER_NAME;
		this.EMAIL=EMAIL;
		this.FIRST_NAME=FIRST_NAME;
		this.LAST_NAME=LAST_NAME;
		this.PASSWORD=PASSWORD;
		this.ACTIVE_FLAG=ACTIVE_FLAG;
		this.CREATED_BY=CREATED_BY;
		this.CREATED_DATE=CREATED_DATE;
	    this.MODIFIED_BY=MODIFIED_BY;
		 this.DOB=DOB;
		 this.POINTS=POINTS;
		 this.NUM_BUSINESS=NUM_BUSINESS;
	}
	 
	 
	 public boolean doConnection(){
		 try {
		 
		 Class.forName(driverName);
		
		 connection = DriverManager.getConnection(URL, "root", "root");
		 } 
		 
		 catch (ClassNotFoundException e) {
		 System.out.println("ClassNotFoundException : "+e.getMessage());
		 return false;
		 }
           catch (SQLException e) {
		 System.out.println(e.getMessage());
		 return false;
		 }
		 return true;
     }
	 
	/* static Connection getNewConnection() throws SQLException, ClassNotFoundException {
	        Class.forName("com.mysql.jdbc.Driver");
	        return DriverManager.getConnection(URL, "root", "root");
	    }
	*/
	 
	 
	 
	/*public void connect(Connection con){
		 
		Statement stmt=con.createStatement();
	}*/
	
	 void createTable(){
	        try {
	         
	            Statement s = connection.createStatement();
	            s.executeQuery("create table USER(USER_ID BIGINT(10) null auto_increment primary key,USER_NAME varchar(64),EMAIL varchar(128),FIRST_NAME varchar(32),LAST_NAME varchar(32),PASSWORD varchar(128),ACTIVE_FLAG varchar(2),CREATED_BY VARCHAR(11) NULL, CREATED_DATE DATETIME NULL,MODIFIED_BY VARCHAR(45) NULL, DOB DATE NULL,POINTS BIGINT(10) NULL,NUM_BUSINESS BIGINT(10)  NULL)");
	           
	        }catch (SQLException e) {

	        	e.printStackTrace();
	        	} 
	        	
	    	catch(Exception e){ System.out.println(e);}
	        
	    }
	 
	 
	 public void print() throws SQLException{
		 Statement stmt = null;
		 String query = "SELECT * FROM USER";
		 stmt = connection.createStatement();
		 ResultSet rs = stmt.executeQuery(query);
		 while(rs.next()) {

			 System.out.print(rs.getInt("USER_ID"));
			 System.out.print("\t \t "+ rs.getString("USER_NAME"));
			 System.out.print("\t\t "+ rs.getString("EMAIL"));
			 System.out.print("\t"+ rs.getString("FIRST_NAME"));
			 System.out.print("\t"+ rs.getString("LAST_NAME"));
			 System.out.print("\t"+ rs.getString("PASSWORD"));
			 System.out.print("\t"+ rs.getString("ACTIVE_FLAG"));
			 System.out.print("\t\t"+ rs.getString("CREATED_BY"));
			 System.out.print("\t\t"+ rs.getDate("CREATED_DATE"));
			 System.out.print(rs.getTime("CREATED_DATE"));
			 System.out.print("\t\t"+rs.getString("MODIFIED_BY"));
			 System.out.print("\t\t"+rs.getDate("DOB"));
			 System.out.print("\t\t"+ rs.getInt("POINTS"));
			 System.out.print("\t\t"+ rs.getInt("NUM_BUSINESS"));

			System.out.println(" ");
			}
	 }
	 
	/*public void createTable(){
String sql= "create table USER(USER_ID BIGINT(10) null auto_increment primary key,USER_NAME varchar(64),EMAIL varchar(128),FIRST_NAME varchar(32),LAST_NAME varchar(32),PASSWORD varchar(128),ACTIVE_FLAG varchar(2),CREATED_BY VARCHAR(11) NULL, CREATED_DATE DATETIME NULL,MODIFIED_BY VARCHAR(45) NULL, DOB DATE NULL,POINTS BIGINT(10) NULL,NUM_BUSINESS BIGINT(10)  NULL)";
 System.out.println("Creating table USER");
 ResultSet rs=stmt.executeQuery(sql);*/
 

	/* public void updateTable(int USER_ID,String USER_NAME,String EMAIL,String FIRST_NAME,String LAST_NAME,String PASSWORD,String ACTIVE_FLAG,String CREATED_BY,Date CREATED_DATE,String MODIFIED_BY ,Date DOB ,int POINTS,int NUM_BUSINESS)throws SQLException
			 {
			 Statement stmt = null;
			 stmt = connection.createStatement();
			 String sql = "UPDATE USER SET '"+ population 
			 +"' WHERE NAME='"+cityName +"' AND 
			 PROVINCE='"+ province +"'";
			 stmt.executeUpdate(sql);
			 stmt.close();
			 }
	
	public void insert(Connection c){
		
		
String sql="insert into user(USER_ID,USER_NAME,EMAIL,FIRST_NAME,LAST_NAME,PASSWORD,ACTIVE_FLAG,CREATED_BY,CREATED_DATE,MODIFIED_BY ,DOB ,POINTS,NUM_BUSINESS) values(this.USER_ID,this.USER_NAME,this.EMAIL,this.FIRST_NAME,this.LAST_NAME,this.PASSWORD,this.ACTIVE_FLAG,this.CREATED_BY,this.CREATED_DATE,this.MODIFIED_BY ,this.DOB ,this.POINTS,this.NUM_BUSINESS);";
ResultSet rs=stmt.executeQuery(sql);

stmt.executeUpdate("insert into user(USER_ID,USER_NAME,EMAIL,FIRST_NAME,LAST_NAME,PASSWORD,ACTIVE_FLAG,CREATED_BY,CREATED_DATE,MODIFIED_BY ,DOB ,POINTS,NUM_BUSINESS) values(2,'user','chandana@gmail.com','chandana','N','c123','y','chandini','1991-06-05 10:50:50','chandana','1944-10-10',45,100)");
stmt.executeUpdate("insert into user(USER_ID,USER_NAME,EMAIL,FIRST_NAME,LAST_NAME,PASSWORD,ACTIVE_FLAG,CREATED_BY,CREATED_DATE,MODIFIED_BY ,DOB ,POINTS,NUM_BUSINESS) values(3,'admin','chand@gmail.com','chandini','haridas','chd','y','chandini','1991-01-01 10:10:00','chandini','1940-01-01',177,1000)");
stmt.executeUpdate("insert into user(USER_ID,USER_NAME,EMAIL,FIRST_NAME,LAST_NAME,PASSWORD,ACTIVE_FLAG,CREATED_BY,CREATED_DATE,MODIFIED_BY ,DOB ,POINTS,NUM_BUSINESS) values(4,'admin','chand@gmail.com','chandini','haridas','chd','y','chandini','1991-01-01 10:10:00','chandini','1940-01-01',189,109)");
stmt.executeUpdate("insert into user(USER_ID,USER_NAME,EMAIL,FIRST_NAME,LAST_NAME,PASSWORD,ACTIVE_FLAG,CREATED_BY,CREATED_DATE,MODIFIED_BY ,DOB ,POINTS,NUM_BUSINESS) values(5,'admin','chand@gmail.com','chandini','haridas','chd','y','chandini','1991-01-01 10:10:00','chandini','1940-01-01',1000,108)");


	}
	*/
	
	public static void main(String[] args)throws Exception {
		
		MySqlFunc con=new MySqlFunc();
	
			System.out.println("Connection : " +con.doConnection());
			try{
				
			con.createTable();
			con.print();
			
			}catch(SQLException ex){System.out.println(ex.getMessage());}
		
	/*try {
		Connection c = getNewConnection();
		o.connect(c);
		o.createTable();
		o.insert();
		
	}
	catch (SQLException e) {

    	e.printStackTrace();
    	} 
    	catch (ClassNotFoundException e) {
    	
    	e.printStackTrace();
    	}
	catch(Exception e){ System.out.println(e);}
	finally{
	
	}
		
		
	}
*/
	}
}
